DataFrame Union Operations

Merging multiple datasets row-wise in PySpark using union and column-aligned unionByName.

What is the Union Operation?

The union() and unionByName() operations concatenate two DataFrames vertically (row-wise), equivalent to UNION ALL in relational SQL.

In PySpark, there is a critical difference in how these two methods align column schemas:

• union() merges based on positional column index. If columns are in a different order, columns will be misaligned, leading to silent data corruption or schema casting errors!
• unionByName() merges based on column name spelling. It is highly recommended and safe because it automatically rearranges columns in the second DataFrame to match the first's column order.

Syntax and Safety Rules

# A. Positional Union (Risky if columns are in different orders!)
df1.union(df2)

# B. Name-Aligned Union (Safe, matches spelling)
df1.unionByName(df2)

# C. Name-Aligned Union with missing columns allowed
# Automatically inserts Null for fields present in only one of the DataFrames
df1.unionByName(df2, allowMissingColumns=True)

Example Usage Pipeline

Below is a complete, copy-paste-ready PySpark script demonstrating unions:

from pyspark.sql import SparkSession

# 1. Setup local Spark session
spark = SparkSession.builder \
    .appName("DataFrame Unions Demo") \
    .master("local[*]") \
    .getOrCreate()

# 2. First Dataset (Columns: id, name, city)
data_1 = [
    (1, "Alice", "Mumbai"),
    (2, "Bob", "Delhi"),
]
columns_1 = ["id", "name", "city"]
df1 = spark.createDataFrame(data_1, columns_1)

# 3. Second Dataset - DIFFERENT Column Order (Columns: city, name, id)
data_2 = [
    ("Bangalore", "Charlie", 3),
    ("Chennai", "David", 4),
]
columns_2 = ["city", "name", "id"]
df2 = spark.createDataFrame(data_2, columns_2)

# 4. Perform unsafe union (column alignment will be corrupt!)
corrupt_union = df1.union(df2)

# 5. Perform safe unionByName (aligned automatically)
aligned_union = df1.unionByName(df2)

# 6. Show results
print("=== Unsafe union() Output (Corrupted Alignments) ===")
corrupt_union.show()

print("=== Safe unionByName() Output (Perfect Alignments) ===")
aligned_union.show()

Rendered Output:

=== Unsafe union() Output (Corrupted Alignments) ===
+---------+---------+---------+
|       id|     name|     city|
+---------+---------+---------+
|        1|    Alice|   Mumbai|
|        2|      Bob|    Delhi|
|Bangalore|  Charlie|        3|
|  Chennai|    David|        4|
+---------+---------+---------+
-- Note: Strings loaded into numeric 'id' and vice-versa. Silent corruption!

=== Safe unionByName() Output (Perfect Alignments) ===
+---+-------+---------+
| id|   name|     city|
+---+-------+---------+
|  1|  Alice|   Mumbai|
|  2|    Bob|    Delhi|
|  3|Charlie|Bangalore|
|  4|  David|  Chennai|
+---+-------+---------+
-- Note: Columns aligned successfully by name.